01: Cleaning twitter data from Crimson Hexagon
Jamie Montgomery
Summary
This script takes the raw data downloaded from Crimson Hexagon and cleans it up for analysis. At the bottom of the script there are a few exploratory maps.
Setup
knitr::opts_chunk$set(message = F, warning = F)
library(tidyverse)
library(jsonlite)
library(ggmap)
library(leaflet)
library(sf)
library(readxl)
library(reticulate)
library(RColorBrewer)
library(kableExtra)
library(mapview)Data cleaning
Crimson Hexagon data is saved in two day bulk exports. The CH website only allows exports of 10,000 randomly selected tweets. There seemed to be between 10-15k over any 2 day period so data was exported in 2-day chunks to try and get as much data as possible. Two filters were applied to the data before downloading - the location was set to Santa Barbara (this does not mean the tweet was geotagged but that it came from the area) and that it was an Original Tweet (not a retweet).
# list all .xlsx files
xl_files <- list.files("../data/daily", pattern = ".xlsx", full.names = TRUE)
ids <- data.frame()
for(i in 1:length(xl_files)){
print(i)
#get twitter IDs from the Crimson Hexagon output
ch_data <- read_excel(xl_files[i], skip = 1) %>%
select(GUID)
ids <- rbind(ch_data, ids)
}
nums <- seq(1, nrow(ids), length.out = 30)
for(i in 1:29){
n <- nums[i]
n2 <- nums[i+1]
df <- ids[n:n2,]
#save as .txt file to be read by the python twarc library
write.table(as.numeric(df$GUID), file = paste0("../data/twitter_ids_", i, ".txt"), sep = "\t",
row.names = FALSE, col.names = FALSE)
}Now I use the python library, twarc in my terminal to “hydrate” the data using the tweet IDs. The Crimson Hexagon data does not give us much information but the twarc library lets us use the twitter id to grab a lot more information (including coordinates for geotagged tweets).
Once this is done, all tweets are saved in a JSON file.
# Give the input file name to the function.#
tweets1 <- stream_in(file("../data/tweets1.jsonl"))
tweets2 <- stream_in(file("../data/tweets2.jsonl"))
tweets3 <- stream_in(file("../data/tweets3.jsonl"))
tweets4 <- stream_in(file("../data/tweets4.jsonl"))
tweets5 <- stream_in(file("../data/tweets5.jsonl"))
tweets6 <- stream_in(file("../data/tweets6.jsonl"))
tweets7 <- stream_in(file("../data/tweets7.jsonl"))
tweets8 <- stream_in(file("../data/tweets8.jsonl"))
tweets9 <- stream_in(file("../data/tweets9.jsonl"))
tweets10 <- stream_in(file("../data/tweets10.jsonl"))
tweets11 <- stream_in(file("../data/tweets11.jsonl"))
tweets12 <- stream_in(file("../data/tweets12.jsonl"))
tweets13 <- stream_in(file("../data/tweets13.jsonl"))
tweets14 <- stream_in(file("../data/tweets14.jsonl"))
tweets15 <- stream_in(file("../data/tweets15.jsonl"))
tweets16 <- stream_in(file("../data/tweets16.jsonl"))
tweets17 <- stream_in(file("../data/tweets17.jsonl"))
tweets18 <- stream_in(file("../data/tweets18.jsonl"))
tweets19 <- stream_in(file("../data/tweets19.jsonl"))
tweets20 <- stream_in(file("../data/tweets20.jsonl"))
tweets21 <- stream_in(file("../data/tweets21.jsonl"))
tweets22 <- stream_in(file("../data/tweets22.jsonl"))
tweets23 <- stream_in(file("../data/tweets23.jsonl"))
tweets24 <- stream_in(file("../data/tweets24.jsonl"))
tweets25 <- stream_in(file("../data/tweets25.jsonl"))
tweets26 <- stream_in(file("../data/tweets26.jsonl"))
tweets27 <- stream_in(file("../data/tweets27.jsonl"))
tweets28 <- stream_in(file("../data/tweets28.jsonl"))
tweets29 <- stream_in(file("../data/tweets29.jsonl")) create_tweet_df <- function(tweets){
#get the columns we want from the json (some are nested)
tweet_df <- as_tibble(cbind(
as.character(tweets$created_at),
as.numeric(tweets$id_str),
as.character(tweets$full_text),
as.numeric(tweets$user$id_str),
as.character(tweets$user$location),
as.character(tweets$geo$type),
as.character(tweets$geo$coordinates),
as.character(tweets$lang),
as.numeric(tweets$retweet_count),
as.numeric(tweets$favorite_count)))
#assign column names
names(tweet_df) <- c("created_at","tweet_id","full_text","user_id","user_location",
"geo_type", "geo_coordinates", "language", "retweet_count", "favorite_count")
## filter
tweets_geo <- tweet_df %>%
filter(!is.na(geo_type)) %>%
mutate(tweet_id = as.numeric(tweet_id),
user_id = as.numeric(user_id),
retweet_count = as.numeric(retweet_count),
favorite_count = as.numeric(favorite_count))
return(tweets_geo)
}Apply function
df1 <- create_tweet_df(tweets1)
df2 <- create_tweet_df(tweets2)
df3 <- create_tweet_df(tweets3)
df4 <- create_tweet_df(tweets4)
df5 <- create_tweet_df(tweets5)
df6 <- create_tweet_df(tweets6)
df7 <- create_tweet_df(tweets7)
df8 <- create_tweet_df(tweets8)
df9 <- create_tweet_df(tweets9)
df10 <- create_tweet_df(tweets10)
df11 <- create_tweet_df(tweets11)
df12 <- create_tweet_df(tweets12)
df13 <- create_tweet_df(tweets13)
df14 <- create_tweet_df(tweets14)
df15 <- create_tweet_df(tweets15)
df16 <- create_tweet_df(tweets16)
df17 <- create_tweet_df(tweets17)
df18 <- create_tweet_df(tweets18)
df19 <- create_tweet_df(tweets19)
df20 <- create_tweet_df(tweets20)
df21 <- create_tweet_df(tweets21)
df22 <- create_tweet_df(tweets22)
df23 <- create_tweet_df(tweets23)
df24 <- create_tweet_df(tweets24)
df25 <- create_tweet_df(tweets25)
df26 <- create_tweet_df(tweets26)
df27 <- create_tweet_df(tweets27)
df28 <- create_tweet_df(tweets28)
df29 <- create_tweet_df(tweets29)Combine
all_df <- bind_rows(df1, df2, df3, df4, df5, df6, df7, df8, df9, df10, df11, df12, df13, df14,df15, df16, df17, df18, df19, df20, df21, df22, df23, df24, df25, df26, df27, df28, df29) Remove points outside of our bounding box, which is c(-119.9,34.38,-119.5,34.48)
# create new df with just the tweet texts & usernames
tweet_data <- all_df %>%
mutate(coords = gsub("\\)|c\\(", "", geo_coordinates)) %>%
separate(coords, c("lat", "lon"), sep = ", ") %>%
mutate_at(c("lon", "lat"), as.numeric) %>%
filter(lat >=33.88 & lat <= 34.6,
lon <= -119.5 & lon >= -120.5) %>%
separate(created_at, into = c("Day", "Year"), sep = 26) %>%
mutate(Year = as.numeric(Year)) %>%
separate(Day, into = c("Day", "Date"), sep = 4) %>%
separate(Date, into = c("Date", "Time"), sep = 7) %>%
separate(Time, into = c("Time", "Extra"), sep = 9) %>%
select(-Extra, -language, -geo_type, -Day) %>%
separate(Date, into = c("Month", "Day"), sep = " ") %>%
mutate(Day = as.numeric(Day)) %>%
mutate(month_num = match(Month,month.abb)) %>%
mutate(date = as.Date(paste0(month_num, "/", Day, "/",Year), tryFormats = "%m/%d/%Y"))
write_csv(tweet_data, "../data/geotagged_sb_tweets.csv")
#remove tweets from Jan-Apr 2015 because of the Twitter user interface change
tweet_data_sub <- tweet_data %>% filter(date > "2015-04-28")
write_csv(tweet_data_sub, "../data/geotagged_sb_tweets_post_apr_2015.csv") Map tweets
Map of all tweets
Turn the tweet_df_w_user_type data frame into a spatial object.
tweet_data <- read_csv("../data/geotagged_sb_tweets_post_apr_2015.csv")
tweet_sf <- tweet_data %>%
st_as_sf(coords = c("lon", "lat")) %>%
st_set_crs(4326)Interactive with cluster markers
#map
map <- leaflet(tweet_data) %>%
# Base groups
addProviderTiles(providers$CartoDB.Positron) %>%
# Overlay groups %>%
addCircleMarkers(data = tweet_data, lng = ~lon, lat = ~lat, popup = ~full_text,
radius = 3, stroke = FALSE, fillOpacity = 0.5, clusterOptions = markerClusterOptions())
mapStatic tweet map
register_google(Sys.getenv("GOOGLE_ACCESS_TOKEN"))
#santa barbara
sb.map <- get_map("santa barbara, california", zoom = 14, maptype = "toner-lite")
ggmap(sb.map, legend="none") +
coord_equal() +
labs(x = NULL, y = NULL) +
theme(axis.text = element_blank()) +
geom_point(data = tweet_data, aes(x = lon, y = lat),
size = 0.55, alpha = 0.2, color = "darkorchid4") +
labs(fill = "User type",
title = "Tweets in downtown Santa Barbara")